package com.example.demo.hotel.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface ViewMapper {

    @Select("SELECT " +
            " date_format(`months`.`month`, '%c月') AS `month`, " +
            " COALESCE ( " +
            "  sum( " +
            "   `hotelrooms`.`orderinfo`.`sumprice` " +
            "  ), " +
            "  0 " +
            " ) AS `totalprice` " +
            "FROM " +
            " ( " +
            "  ( " +
            "   ( " +
            "    SELECT " +
            "     ( " +
            "      '2023-01-01' + INTERVAL (`numbers`.`n` - 1) MONTH " +
            "     ) AS `month` " +
            "    FROM " +
            "     ( " +
            "      SELECT " +
            "       1 AS `n` " +
            "      UNION " +
            "       SELECT " +
            "        2 AS `2` " +
            "       UNION " +
            "        SELECT " +
            "         3 AS `3` " +
            "        UNION " +
            "         SELECT " +
            "          4 AS `4` " +
            "         UNION " +
            "          SELECT " +
            "           5 AS `5` " +
            "          UNION " +
            "           SELECT " +
            "            6 AS `6` " +
            "           UNION " +
            "            SELECT " +
            "             7 AS `7` " +
            "            UNION " +
            "             SELECT " +
            "              8 AS `8` " +
            "             UNION " +
            "              SELECT " +
            "               9 AS `9` " +
            "              UNION " +
            "               SELECT " +
            "                10 AS `10` " +
            "               UNION " +
            "                SELECT " +
            "                 11 AS `11` " +
            "                UNION " +
            "                 SELECT " +
            "                  12 AS `12` " +
            "     ) `numbers` " +
            "   ) " +
            "  ) `months` " +
            "  LEFT JOIN `hotelrooms`.`orderinfo` ON ( " +
            "   ( " +
            "    ( " +
            "     MONTH ( " +
            "      `hotelrooms`.`orderinfo`.`checkindate` " +
            "     ) = MONTH (`months`.`month`) " +
            "    ) " +
            "    AND ( " +
            "     YEAR ( " +
            "      `hotelrooms`.`orderinfo`.`checkindate` " +
            "     ) = YEAR (`months`.`month`) " +
            "    ) " +
            "   ) " +
            "  ) " +
            " ) " +
            "GROUP BY " +
            " `months`.`month` " +
            "ORDER BY " +
            " `months`.`month`")
    List<Map<String, Object>> getViewOrder();

    @Select("select `roomstatusinfo`.`roomtype` AS `roomtype`,sum((case when (`roomstatusinfo`.`status` = '空闲') then 1 else 0 end)) AS `vacant`,sum((case when (`roomstatusinfo`.`status` = '入住') then 1 else 0 end)) AS `occupied`,sum((case when (`roomstatusinfo`.`status` = '清洁') then 1 else 0 end)) AS `cleaned` from `roomstatusinfo` group by `roomstatusinfo`.`roomtype`")
    List<Map<String, Object>> getViewRoomStatus();

    @Select("select `registrationinfo`.`roomtype` AS `roomtype`,sum(`registrationinfo`.`price`) AS `total_price` from `registrationinfo` where (year(`registrationinfo`.`checkindate`) = 2023) group by `registrationinfo`.`roomtype`")
    List<Map<String, Object>> getViewRoomType();
}
